<?php

namespace App\Http\Controllers;

use App\Models\Team;
use App\Models\UserProject;
use App\Models\UserTeam;
use App\Models\WechatTempMsg;
use Illuminate\Http\Request;
use App\Models\Volunteer;
use App\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Mockery\Exception;
use Maatwebsite\Excel\Facades\Excel;
use App\Libs\CommonUtils;

class VolunteerController extends Controller
{
    public function index(Request $request){
        $perPage = $request->get('perPage', 10);
        $keyword = $request->get('keyword', '');
        $asExcel = $request->get('asExcel', false);
        $user = $request->user();

//        @文杰,你自己把代码调通,仅供参考
        // 好的，谢谢，邱凯
        $query = DB::table('volunteer')
            ->leftjoin('user','user.id', '=', 'volunteer.user_id')->where('volunteer.in_blacklist', '=', 0);

        switch($user->type)
        {
            case 2:
                $query = $query->leftjoin('user_team','user_team.user_id', '=', 'volunteer.user_id')
                    ->where('user_team.team_id', '=', $user->team_id)
                    ->where('user_team.verify_status', '=', 1);
                break;
            case 3:
                $query = $query->where('volunteer.org_id', '=', $user->org_id);
                break;
        }

        if ($request->has('group_id')) {
            $query = $query->leftjoin('user_group', 'user_group.user_id', '=', 'volunteer.user_id')
                ->where('user_group.volunteer_group_id', '=', $request->get('group_id'));
        }

        if ($user->type == 4 && $request->has('org_id')) {
            $query = $query->where('volunteer.org_id', '=', $request->get('org_id'));
        }
        if ($user->type == 5) {
            $org_list = $user->platform->org_list;
            $query = $query->whereIn('volunteer.org_id', $org_list);
        }

        if($request->get('familyId'))
        {
            $query = $query->where('volunteer.family_id', $request->get('familyId'));
        }

        if ($request->get('stars_verify_status') === '0' || $request->get('stars_verify_status')) {
            $query = $query->where('volunteer.stars_verify_status', $request->get('stars_verify_status'));
        }

        if ($request->get('stars_verify_num')) {
            $query = $query->where('volunteer.stars_verify_num', $request->get('stars_verify_num'));
        }

        $query = $query->select('volunteer.*', 'user.username','user.avatars');
//        Log::info($query->toSql());
        if(!empty($keyword))
        {
            $query = $query->where(function($query) use ($keyword){
                $query->where('volunteer.phone', 'like', "%{$keyword}%")
                    ->orwhere('volunteer.real_name', 'like', "%{$keyword}%")
                    ->orWhere('volunteer.id_number', 'like', "%{$keyword}%")
                    ->orWhere('user.username', 'like', "%{$keyword}%");
            });
        }

        if ($request->has('team_id')) {
            $userIds = [];
            foreach (UserTeam::where('team_id', '=', $request->get('team_id'))->select('user_id')->get()->toArray() as $item) {
                $userIds[] = $item['user_id'];
            }
            $query->whereIn('user.id', $userIds);
        }

        $query->orderby('volunteer.id', 'asc');
        $data = null;
        // 如果是导出，则全部导出，不进行分页
        if($asExcel){

            // 平台限制导出长度，不然内存超出
            if($user->type===4 || $user->type === 5){
                $tmp = $query->offset(1)->limit(2000)->get()->toArray();

            } else{
                $tmp = $query->get()->toArray();
            }
            // 为了保持和分页一致，存入数组中
            $data = array(
                'data' => $tmp
            );
        }else {
            $data = $query->paginate($perPage)->toArray();
        }
//        dd($data);
//        Log::info($data['total']);
//        dd(gettype($data['data'][0]), $data['data'][0]);
        /**  这里就是 用groupby实现查找需要user_id的project_count 但这方法有个问题，查找user_id如果在user_project表中没有记录将不会进行匹配*/
        $ids = array_map(function(&$item){
            // 将project_count 设为0
            $item->project_count = 0;
            $item->good_at = [];
            // 根据身份证 计算年龄
            if(!empty($item->id_number) && CommonUtils::isIdCard($item->id_number)){
                $item->age = CommonUtils::get_age($item->id_number);
                $item->sex = CommonUtils::get_sex($item->id_number);
            } else if (!empty($item->birthday)){
                $item->age = intval(date('Y')) - intval(explode('-',$item->birthday)[0]) + 1;
            } else {
                $item->age = '-';
            }
            return $item->user_id;
        },$data['data']);
        $projectInfo=DB::table('user_project')->select(DB::raw('count(*) as project_count'), 'user_id')
            ->whereIn('user_id', $ids)->where('verify_status', '=', 1)
            ->groupby('user_id')->orderby('user_id')->get()->toArray();

        $teamList = array();

        foreach (UserTeam::whereIn('user_id', $ids)->where('verify_status', '=', '1')->with('team')->get() as $userTeam) {
            if (!isset($teamList[$userTeam->user_id])) {
                $teamList[$userTeam->user_id] = array();
            }

            if (!$userTeam->team) {
                continue;
            }

            $teamList[$userTeam->user_id][] = [
                'name' => $userTeam->team->name,
                'id' => $userTeam->team->id,
                'org_id' => $userTeam->team->org_id
            ];
        }

        $goodAt = DB::table('volunteer_good_at')->select('user_id', 'good_at_name')
            ->whereIn('user_id', $ids)->orderby('user_id')->get()->toArray();

        for($j = 0;$j<count($data['data']);$j++) {
            $item = $data['data'][$j];
            $item->team = isset($teamList[$item->user_id]) ? $teamList[$item->user_id] : [];

            for ($i = 0; $i < count($projectInfo); $i++) {
                if ($data['data'][$j]->user_id == $projectInfo[$i]->user_id) {
//                    Log::info($projectInfo[$i]->project_count);
                    $data['data'][$j]->project_count = $projectInfo[$i]->project_count;
                }
            }
            for ($i = 0; $i < count($goodAt); $i++) {
                if ($data['data'][$j]->user_id == $goodAt[$i]->user_id) {
                    array_push($data['data'][$j]->good_at, $goodAt[$i]->good_at_name);
                }
            }
        }
        
        
        // if($user->type == 2) {
        //     // 当前管理员为团队管理员，需要额外返回团队信息
        //     $teamInfo = Team::where('id', '=',$user->team_id)->get()->toArray();
        //     foreach ($data['data'] as $item) {
        //         $item->team = $teamInfo[0];
        //     }
        // }
        

        $res = $data['data'];
//        dd(gettype($res[0]), $res[0]);
        $extendsConfig = null;
        if ($user->type!== 4 && $user->type !== 5
            && ($config = $user->org->custom_config)
            && ($extendsConfig = $config->extends)
        ) {
            $extendsKey = array_map(function ($item) {
                return $item->key;
            }, $extendsConfig);

            foreach ($res as $item) {
                $itemExtends = $item->extends;
                if (!$itemExtends || is_null($itemExtends)) continue;
                try {
                    $itemExtends = json_decode($itemExtends);
                } catch (\Exception $e) {

                }

                foreach ($extendsKey as $key) {
                    if (isset($itemExtends->$key)) {
                        $item->$key = $itemExtends->$key;
                    }
                }
            }
        }




        // 需要文件的话，就导出为文件，否则返回json字符串
        if($asExcel){
            Excel::create(time(),function($excel) use ($res, $extendsConfig){

                $excel->sheet('volunteer', function($sheet) use ($res, $extendsConfig){
//                    $sheet->appendRow(1, array(
//                        'id', 'user_id', 'org_id', 'username', 'avatars', 'real_name', 'nation', 'sex', 'birthday', 'age', 'phone', 'id_number',
//                        'identifier', 'slogan', 'reward_time', 'province_id', 'province_name',
//                        'city_id', 'city_name', 'county_id', 'county_name', 'addr', 'family_id', 'join_family_time',
//                         'project_count'
//                    ));
                    $excelLabels = array(
                        '志愿者ID', '用户名', '姓名', '民族', '性别', '年龄', '手机号',
                        '身份证号', '所在省份', '所在城市', '所在区县', '详细地址', '参与项目总数', '志愿总时长', '注册时间'
                    );
                    if ($extendsConfig && count($extendsConfig) > 0) {
                        array_map(function($cusItem)use(&$excelLabels){
                            array_push($excelLabels, $cusItem->label);
                        }, $extendsConfig);
                    }


                    $sheet->appendRow(1, $excelLabels);

                    for ($i = 0;$i<count($res);$i++){
                        $tmp = $res[$i];
                        if ($tmp->sex==1) {
                            $sex = '男';
                        } elseif ($tmp->sex==2) {
                            $sex = '女';
                        } else {
                            $sex = '未知';
                        }
                        $excelValue = array(
                            $tmp->id, $tmp->username,
                            $tmp->real_name, $tmp->nation, $sex, $tmp->age,
                            $tmp->phone, $tmp->id_number ? substr($tmp->id_number, 0, 6).'xxxxxxxx'.substr($tmp->id_number, -4) : '',
                            $tmp->province_name, $tmp->city_name,
                            $tmp->county_name, $tmp->addr,
                            $tmp->project_count,  $tmp->reward_time, $tmp->created_at);

                        if ($extendsConfig && count($extendsConfig) > 0) {
                            array_map(function($extendsItem)use(&$excelValue, $tmp){
                                $cusKey = $extendsItem->key;
                                if(isset($tmp->$cusKey)){
                                    array_push($excelValue, $tmp->$cusKey);
                                }
                            }, $extendsConfig);
                        }
                        $sheet->appendRow($i+2,$excelValue);
                    }
                });
            })->export('xls');
        }else {
            return $this->returnJSON([
                'count' => $data['total'],
                'rows' => $res
            ]);
        }
    }

    public function show($id){
        $node = Volunteer::findOrFail($id);
        $data = $node->toArray();
        if (isset($data['user_id'])) {
            $user = User::findOrFail($data['user_id']);
            $data['username'] = $user->username;
            $data['avatars'] = $user->avatars;
            $data['project_count'] = DB::table('user_project')->select((DB::raw('count(id) as project_count')))
                ->where('user_id', '=', $data['user_id'])->where('verify_status', '=', 1)
                ->first()->project_count;
            $data['team_count'] = DB::table('user_team')->where('verify_status', 1)
                ->where('user_id', $data['user_id'])->count();
        }
        // 根据身份证 计算年龄
        if(!empty($data['id_number']) && CommonUtils::isIdCard($data['id_number'])){
            $data['age'] = CommonUtils::get_age($data['id_number']);
            $data['sex'] = CommonUtils::get_sex($data['id_number']);
        } else if (!empty($data['birthday'])){
            $data['age'] = intval(date('Y')) - intval(explode('-',$data['birthday'])[0]) + 1;
        } else {
            $data['age'] = '-';
        }
        $data['id_number'] = $data['id_number'] ? substr($data['id_number'], 0, 6).'xxxxxxxx'.substr($data['id_number'], -4) : '';
        return $this->returnJSON($data, 0, 'ok');
    }

    public function update(Request $request, $id){
//        $this->validate($request, [
//            'id_number' => 'required|max:18',
//            'real_name' => 'required|max:32',
//            'nation' => 'max:32',
//            'address' => 'required',
//            'phone' => 'required',
//            'pwd' => 'max:64',
//        ]);
        $node = Volunteer::findOrFail($id);
        $data = $request->only([
            'id_number', 'real_name', 'nation', 'address', 'phone', 'pwd', 'stars'
        ]);
        if (isset($data['pwd'])) {
            if ($node->user_id) {
                $user = User::findOrFail($node->user_id);
                $user->fill([
                    'pwd' => bcrypt($data['pwd'])
                ]);
                $user->save();
            }
        }
        $node->fill($data);
        if ($node->save()) {
            return $this->returnJSON(null, 0, '保存成功');
        }
        return $this->returnJSON(null, 500, '保存失败');
    }

    public function project(Request $request, $id) {
        $perPage = $request->get('perPage', 10);
        $volunteerInfo = Volunteer::findOrFail($id);
        $query = UserProject::with('project.team')
            ->where('user_id', $volunteerInfo->user_id)
            ->where('verify_status', 1);
        $data = $query->paginate($perPage)->toArray();
        return $this->returnJSON([
            'count' => $data['total'],
            'rows' => $data['data']
        ]);
    }

    public function team(Request $request, $id) {
        $perPage = $request->get('perPage', 10);
        $volunteerInfo = Volunteer::findOrFail($id);
        $query = UserTeam::with('team')
            ->where('user_id', $volunteerInfo->user_id)
            ->where('verify_status', 1);
        $data = $query->paginate($perPage)->toArray();
        return $this->returnJSON([
            'count' => $data['total'],
            'rows' => $data['data']
        ]);
    }

    public function applylist(Request $request){
        $user = $request->user();
        // 只允许团队管理员进行操作
        if ($user->type!=2){
            return $this->returnJSON(null, 1, '需要团队管理员权限才能进行操作');
        }
        $keyword = $request->get('keyword', '');
        $perPage = $request->get('perPage', 10);
        $query = DB::table('volunteer')->leftjoin('user_team','user_team.user_id', '=', 'volunteer.user_id')
            ->leftjoin('user','user.id', '=', 'volunteer.user_id')
            ->select(DB::Raw('volunteer.id as volunteer_id'), 'volunteer.user_id',
                'volunteer.org_id', 'volunteer.real_name', 'volunteer.nation', 'volunteer.sex', 'volunteer.birthday',
                'volunteer.phone', 'volunteer.identifier', 'volunteer.slogan',
                'volunteer.reward_time', 'volunteer.id_number', 'volunteer.province_id', 'volunteer.province_name',
                'volunteer.city_id', 'volunteer.city_name', 'volunteer.county_id', 'volunteer.county_name', 'volunteer.addr',
                'volunteer.family_id', 'volunteer.join_family_time', 'volunteer.last_login_time', 'user_team.created_at',
                'volunteer.updated_at', 'user_team.verify_status', 'user_team.verify_msg',
                'user.username','user.avatars',DB::Raw('user_team.id as id'));

//        $query = UserTeam::with(['team', 'volunteer.volunteer'])->where('verify_status', '=', 0);

        switch($user->type) {
            case 2:
                $query = $query->where('user_team.team_id', '=', $user->team_id);
                break;
            case 3:
                $query = $query->where('volunteer.org_id', '=', $user->org_id);
                break;
        }

        if (!empty($keyword)){
            $query = $query->where(function($query) use ($keyword){
                $query->where('volunteer.phone', 'like', "%{$keyword}%")
                    ->orWhere('volunteer.id_number', 'like', "%{$keyword}%")
                    ->orWhere('volunteer.real_name', 'like', "%{$keyword}%")
                    ->orWhere('user.username', 'like', "%{$keyword}%");
            });
        }

        $query->where('user_team.verify_status', '<>', -1);
        if ($request->has('status')) {
            $query->where('user_team.verify_status', '=', $request->get('status'));
        }

        $query->orderby('volunteer.id', 'asc');
        $data = $query->paginate($perPage)->toArray();
//        Log::info($data);

        /**  这里就是 用groupby实现查找需要user_id的project_count 但这方法有个问题，查找user_id如果在user_project表中没有记录将不会进行匹配*/
        $ids = array_map(function($item){
            // 将project_count 设为0
            $item->project_count = 0;
            return $item->user_id;
        },$data['data']);
        $projectInfo=DB::table('user_project')->select(DB::raw('count(*) as project_count'), 'user_id')
            ->whereIn('user_id', $ids)->where('verify_status', '=', 1)
            ->groupby('user_id')->orderby('user_id')->get()->toArray();

//        Log::info($projectInfo);

        for($i = 0;$i<count($projectInfo);$i++){
            for($j = 0;$j<count($data['data']);$j++){
                if ($data['data'][$j]->user_id == $projectInfo[$i]->user_id){
                    $data['data'][$j]->project_count = $projectInfo[$i]->project_count;
                }
            }
        }

        if($user->type == 2) {
            // 当前管理员为团队管理员，需要额外返回团队信息
            $teamInfo = Team::where('id', '=',$user->team_id)->get()->toArray();
            foreach ($data['data'] as $item) {
                $item->team = $teamInfo[0];
                if (!empty($item->birthday)){
                    // 计算年龄
                    $item->age = intval(date('Y')) - intval(explode('-',$item->birthday)[0]) + 1;
                }else{
                    $item->age = 0;
                }
            }
        }else{
            foreach ($data['data'] as $item) {
//                print(gettype($item->birthday));
                if (!empty($item->birthday)){
                    // 计算年龄
                    $item->age = intval(date('Y')) - intval(explode('-',$item->birthday)[0]) + 1;
                }else{
                    $item->age = 0;
                }
            }
        }

        return $this->returnJSON([
            'count' => $data['total'],
            'rows' => $data['data']
        ]);
    }


    public function destroy(Request $request, $userIds){
        // 只有团队管理员才能删除，只移除user_team里的行
        $user = $request->user();
        if ($user->type!=2){
            return $this->returnJSON(null, 1, '需要团队管理员权限才能进行移除志愿者');
        }
        $ids = array_map(function($item){
            return intval($item);
        }, explode(',', $userIds));
        $result = DB::table('user_team')->where('team_id', '=', $user->team_id)->whereIn('user_id', $ids)->delete();
        if ($result) {
            $team = Team::findOrFail($user->team_id);
            $tmp = UserTeam::where('verify_status', '=', 1)
                ->where('team_id', '=', $user->team_id)
                ->selectRaw('count(*) as cnt')->get()->toArray();
            $team_size = 0;
            if (isset($tmp)&&count($tmp)>0){
                $team_size = $tmp[0]['cnt'];
            }
            $team->fill([
                'team_size' => $team_size
            ]);
            $team->save();
            return $this->returnJSON($ids, 0, '删除成功');
        } else {
            return $this->returnJSON(null, 1, '删除失败');
        }
    }

    public function accept(Request $request, $id){
        $user = $request->user();
        if ($user->type!=2){
            return $this->returnJSON(null, 1, '需要团队管理员权限才能进行操作');
        }
        $ids = array_map(function($item){
            return intval($item);
        },explode(',', $id));

        $result = UserTeam::whereIn('id', $ids)->where('verify_status', '<>', '1')->update(['verify_status' => 1]);
        if ($result) {
            $team = Team::findOrFail($user->team_id);
            $tmp = UserTeam::where('verify_status', '=', 1)
                ->where('team_id', '=', $user->team_id)
                ->selectRaw('count(*) as cnt')->get()->toArray();
            $team_size = 0;
            if (isset($tmp)&&count($tmp)>0){
                $team_size = $tmp[0]['cnt'];
            }
            $team->fill([
                'team_size' => $team_size
            ]);
            $team->save();

            // 发送站内信
            $userInfo = DB::table('user_team')->whereIn('id', $ids)->get()->toArray();
            $info = User::with(['team'])->where('id', '=', $user->id)->get()->toArray()[0];
            Log::info($info);
            $content = '您已成功加入《'.$info['team']['name'].'》，与团队小伙伴一起来做公益吧';
            $nodes = array_map(function ($item) use ($user, $content, $request){

                //微信模版消息，申请加入团队被录取
                WechatTempMsg::add(3, ['user_team_id'=>$item->id]);

                return array('from_user_id'=>$user->id,
                    'title' => $request->get('title', ''),
                    'to_user_id'=>$item->user_id,
                    'type' => 1,
                    'content' => $content,);
            }, $userInfo);


            DB::table('user_message')->insert($nodes);

            // 发送短信
//            $ids = array_map(function($item){
//                return $item->user_id;
//            }, $userInfo);
//            $sms = new \App\Libs\Sms();
//            $userList = User::whereIn('id',  $ids)->get();
//            foreach($userList as $item)
//            {
//                $ret = $sms->sendTemplateSMS($item->phone, array($content),209756);
//            }

            return $this->returnJSON($ids, 0, '通过申请，成功');
        } else {
            return $this->returnJSON(null, 1, '通过申请，失败');
        }
    }

    public function reject(Request $request, $id){
        $user = $request->user();
        if ($user->type!=2){
            return $this->returnJSON(null, 1, '需要团队管理员权限才能进行操作');
        }
        $ids = array_map(function($item){
            return intval($item);

        }, explode(',', $id));
        $result = UserTeam::whereIn('id', $ids)
            ->where('verify_status', '<>', '2')
            ->update(['verify_status' => 2, 'verify_msg' => $request->input('reason')]);
//        Log::info($result);
        if ($result) {
            // 发送站内信
            $userInfo = DB::table('user_team')->whereIn('id', $ids)->get()->toArray();
            $info = User::with(['team'])->where('id', '=', $user->id)->get()->toArray()[0];
//            Log::info($info);
            $content = '您申请参加的团队《'.$info['team']['name'].'》审核不通过，去看看其他更合适自己的团队吧';
            $nodes = array_map(function ($item) use ($user, $content, $request){

                //微信模版消息，申请加入团队被拒绝
                WechatTempMsg::add(4, ['user_team_id'=>$item->id]);
                return array('from_user_id'=>$user->id,
                    'title' => $request->get('title', ''),
                    'to_user_id'=>$item->user_id,
                    'type' => 1,
                    'content' => $content,);
            }, $userInfo);
            DB::table('user_message')->insert($nodes);

            // 发送短信
//            $ids = array_map(function($item){
//                return $item->user_id;
//            }, $userInfo);
//            $sms = new \App\Libs\Sms();
//            $userList = User::whereIn('id',  $ids)->get();
//            foreach($userList as $item)
//            {
//                $ret = $sms->sendTemplateSMS($item->phone, array($content),209756);
//            }
            return $this->returnJSON($ids, 0, '拒绝申请，成功');
        } else {
            return $this->returnJSON(null, 1, '拒绝申请，失败');
        }
    }

    public function notify(Request $request, $userIds=null){
        $user = $request->user();
        $content= $request->get('content', '');
        if($userIds!=null) {
            $data = array_map(function ($item) use($user,$request) {
                //微信模版消息，站内信
                WechatTempMsg::add(9, ['user_id'=>$item]);
                return array('from_user_id'=>$user->id,
                    'to_user_id' => intval($item),
                    'title' => $request->get('title', ''),
                    'type' => 1,
                    'content' => $request->get('content', ''));
            }, explode(',', $userIds));
            DB::table('user_message')->insert($data);


        }else{
            $query = null;
            switch($user->type) {
                case 2:
                    $team = DB::table('team')->select('id')->where('user_id', '=', $user->id)->first();
                    $query = DB::table('user_team')->select('user_id')
                        ->where('team_id', '=', $team->id)
                        ->where('in_blacklist', '=', 0);
                    break;
                case 3:
                    $query = DB::table('volunteer')->select('user_id')->where('volunteer.org_id', '=', $user->org_id);
                    break;
                case 4:
                    $query = DB::table('volunteer')->select('user_id');
                    break;
                case 5:
                    $query = DB::table('volunteer')->select('user_id');
            }
            $allId = $query->get()->toArray();
            $data = array_map(function ($item) use($user,$content, $request) {
                //微信模版消息，站内信
                WechatTempMsg::add(9, ['user_id'=>$item->user_id]);

                return array('from_user_id'=>$user->id,
                    'from_user_name' => $user->username,
                    'to_user_id' => intval($item->user_id),
                    'title' => $request->get('title', ''),
                    'type' => 1,
                    'content' => $content);
            }, $allId);
            DB::table('user_message')->insert($data);
        }
        return $this->returnJSON(null, 0, '发送成功');
    }

    public function import(Request $request) {
        set_time_limit(0);

        $this->validate($request, [
            'file' => 'required'
        ]);
        $org_id = $request->has('org_id') ? $request->input('org_id') : 0;
        $team_id = $request->has('teamId') ? $request->input('teamId') : 0;
        $user = $request->user();
        if ($user->type ==3) {
            $org_id = $user->org_id;
        } else if ($user->type ==2) {
            $team = $user->team;
            $org_id = $team->org_id;
            if (!$team_id) {
                $team_id = $team->id;
            }
        }
        $file = public_path($request->input('file'));
        if (!file_exists($file)) {
            throw new \Exception('文件上传出错');
        }
        $reader = Excel::load($file);
        $rows = [];
        $phones = [];
        $idNumbers = [];
        $errors = [];
        $inserts = [];
        $saved = 0;
        $skiped = 0;
        $duplicated = 0;
        foreach ($reader->toArray() as $row) {
            $item = [
                'nation' => isset($row['民族']) ? $row['民族'] : (isset($row['名族']) ? $row['名族'] : ''),
                'real_name' => isset($row['姓名']) ? $row['姓名'] : '',
                'phone' => isset($row['手机']) && !empty($row['手机']) ? trim($row['手机']) : (isset($row['电话']) && !empty($row['电话']) ? trim($row['电话']) : ''),
                'id_number' => isset($row['身份证号']) ? $row['身份证号'] : '',
                'email' => isset($row['邮箱']) ? $row['邮箱'] : '',
            ];
            if (
                empty($item['phone'])
                || empty($item['real_name'])
                || empty($item['id_number'])
            ) {
                $errors[] = $item;
                $skiped++;
                continue;
            }
            if (!preg_match('#^\d{11}$#', $item['phone'])) {
                $errors[] = $item;
                $skiped++;
                continue;
            } else if (!checkIdCard($item['id_number'])) {
                $errors[] = $item;
                $skiped++;
                continue;
            }

            if (array_search((string)$item['phone'], $phones) === false && array_search((string)$item['id_number'], $idNumbers) === false) {
                $phones[] = (string)$item['phone'];
                $idNumbers[] = (string)$item['id_number'];
            } else {
                // 导入的信息有重复的phone，或者重复的id_number
                $errors[] = $item;
                $skiped++;
                continue;
            }

            $rows[] = $item;
        }
        $existsUser = User::whereIn('phone', $phones)
            ->where('org_id', $org_id)
            ->where('type', 1)
            ->select('phone', 'id')->get()->toArray();
        $exists = [];
        $existsMap = [];
        foreach ($existsUser as $info) {
            $exists[] = (string)$info['phone']; // 所有user表已有用户phone
            $existsMap[(string)$info['phone']] = $info['id'];
        }
        $volUser = []; // user表中有该志愿者，而Volunteer表中没有，则需要单独添加
        $volBoth = []; // 记录user和Volunteer表都存在的用户

        // 根据phone判断Volunteer表是否有记录
        $existsByPhoneVol = array_map(function($item) {
            return (string) $item['phone'];
        }, Volunteer::whereIn('phone', $phones)->where('org_id', $org_id)->select('phone')->get()->toArray()); // volunteer 中存在的phone

        // 根据phone判断Volunteer表是否有记录
        $existsByIDNumberVol = array_map(function($item) {
            return (string) $item['id_number'];
        }, Volunteer::whereIn('id_number', $phones)->where('org_id', $org_id)->select('id_number')->get()->toArray()); // volunteer 中存在的phone

        foreach ($rows as $key => $data) {
            if (array_search($data['phone'], $exists) !== false) {
                // 判断是否需要添加到Volunteer表中
                if (array_search($data['phone'], $existsByPhoneVol) === false
                    && array_search($data['id_number'], $existsByIDNumberVol) === false
                    && array_key_exists((string)$data['phone'], $existsMap)) {
                    $id = $data['id_number']."";
                    $birthday = substr($id, 6, 4).'-'.substr($id, 10, 2).'-'.substr($id, 12, 2);
                    $sex = (intval(substr($id, 16, 1)) % 2) ? 1 : 2;

                    $volUser[] = [
                        'org_id' => $org_id,
                        'nation' => isset($data['nation']) ? $data['nation'] : '',
                        'real_name' => $data['real_name'],
                        'phone' => $data['phone'],
                        'id_number' => $data['id_number'],
                        'user_id' => $existsMap[$data['phone']],
                        'identifier' => 'V'.date('ymd').sprintf('%06d', $existsMap[$data['phone']]),
                        'birthday' => $birthday,
                        'sex' => $sex
                    ];
                } else {
                    if (array_key_exists((string)$data['phone'], $existsMap)) {
                        $volBoth[] = [
                            'user_id' => $existsMap[(string)($data['phone'])]
                        ];
                    }

//                    $skiped++;
//                    $duplicated++;
                }
            }  else {// 两个表都不存在该phone的值，接着需要判断Volunteer表中id_number的值
                if (array_search($data['id_number'], $existsByIDNumberVol) === false) {
                    $exists[] = $data['phone'];
                    $inserts[] = $data;
                } else { // 如果Volunteer表中存在id_number这条记录，说明当前插入的记录非法，过滤掉
                    $skiped++;
                    $errors[] = $data;
                }

            }
        }
//        dd($volBoth);

//        dd($team_id, $existsVol, $volBoth, $volUser, $existsUser, $rows);
        if (count($volUser)) {
            // 将user表中存在的，但Volunteer表中不存在的用户，添加到Volunteer表中
//            Log::info('成功插入user_id为： ');
//            Log::info($volUser);
            DB::table('volunteer')->insert($volUser);
        }
        // 接着获取excel所有user表中有记录的用户，添加到user_team表中去
        $userIds = array_map(function($item) {
            return (string)$item['user_id'];
        }, $volUser);
        foreach ($volBoth  as $item) {
            $userIds[] = $item['user_id'];
        }
        $userTeamUserId = array_map(function($item) {
            return (string)$item['user_id'];
        }, UserTeam::whereIn('user_id', $userIds)->select('user_id')->where('team_id', $team_id)->get()->toArray());
//        dd($userTeamUserId);
        $userTeam = [];
        if ((int) $team_id != 0) {
            foreach ($userIds as $userId) {
                if (array_search($userId, $userTeamUserId) !== false) continue; // 如果user_team存在该记录则跳过
                $userTeam[] = [
                    'user_id' => $userId,
                    'team_id' => $team_id,
                    'verify_status' => '1',
                    'verify_msg' => '团队导入，自动通过'
                ];
            }
            DB::table('user_team')->insert($userTeam);
        }

        if (!count($inserts)) {
            $operationNum = count($userTeam) > count($volUser) ? count($userTeam) : count($volUser);
            $totalSkipped = count($rows) - $operationNum + count($errors);
            $totalDuplicated = count($rows) - $operationNum;
            return $this->returnJSON([
                'saved' => $operationNum,
                'skiped' => $totalSkipped,
                'errors' => $errors,
                'duplicated' => $totalDuplicated
            ], 0, ($operationNum ? '导入成功'.$operationNum.'条数据。' : '没有要导入的！').($totalSkipped ? '其中总共有'.$totalSkipped.'条数据被跳过。' : '').($totalDuplicated ? ''.$totalDuplicated.'条数据重复被跳过。' : ''));
        }
        $pwd = bcrypt('88888888');
        foreach ($inserts  as $data) {

            $userData[] = [
                'type' => '1',
                'org_id' => $org_id,
                'team_id' => $team_id,
                'username' => $data['phone'],
                'email' => $data['email'],
                'phone' =>  $data['phone'],
                'pwd' => $pwd
            ];
        }
        DB::table('user')->insert($userData);
        $add = User::where('org_id', $org_id)->where('type', 1)->whereIn('phone', array_map(function($item) {
            return $item['phone'];
        }, $userData) )->get();

        foreach($add as $key=>$value)
        {
            $newUser[$value->phone] = $value;
        }

        if ((int) $team_id != 0) {
            foreach ($inserts as $data) {

                $userTeam[] = [
                    'user_id' => $newUser[$data['phone']]->id,
                    'team_id' => $team_id,
                    'verify_status' => '1',
                    'verify_msg' => '团队导入，自动通过'
                ];
            }
            DB::table('user_team')->insert($userTeam);
        }

        foreach ($inserts  as $data) {
            $id = $data['id_number']."";
            $birthday = substr($id, 6, 4).'-'.substr($id, 10, 2).'-'.substr($id, 12, 2);
            $sex = (intval(substr($id, 16, 1)) % 2) ? 1 : 2;

            $volunteer[] = [
                'org_id' => $org_id,
                'nation' => isset($data['nation']) ? $data['nation'] : '',
                'real_name' => $data['real_name'],
                'phone' => $data['phone'],
                'id_number' => $data['id_number'],
                'user_id' => $newUser[$data['phone']]->id,
                'identifier' => 'V'.date('ymd').sprintf('%06d', $newUser[$data['phone']]->id),
                'birthday' => $birthday,
                'sex' => $sex
            ];
        }
        DB::table('volunteer')->insert($volunteer);

        //foreach ($inserts  as $data) {
        //    $id = $data['id_number']."";
        //    $birthday = substr($id, 6, 4).'-'.substr($id, 10, 2).'-'.substr($id, 12, 2);
        //    $sex = (intval(substr($id, 16, 1)) % 2) ? 1 : 2;
        //    $user = new User();
        //    $user->fill([
        //        'type' => '1',
        //        'org_id' => $org_id,
        //        'team_id' => $team_id,
        //        'username' => $data['phone'],
        //        'email' => $data['email'],
        //        'phone' =>  $data['phone'],
        //        'pwd' => bcrypt('88888888')
        //    ]);
        //    if (!$user->save()) {
        //        continue;
        //    }
        //    $relation = null;
        //    if ($team_id) {
        //        $relation = new UserTeam();
        //        $relation->fill([
        //            'user_id' => $user->id,
        //            'team_id' => $team_id,
        //            'verify_status' => '1',
        //            'verify_msg' => '团队导入，自动通过'
        //        ]);
        //    }
        //    $node = new Volunteer();
        //    $node->fill([
        //        'org_id' => $org_id,
        //        'nation' => $data['nation'],
        //        'real_name' => $data['real_name'],
        //        'phone' => $data['phone'],
        //        'id_number' => $data['id_number'],
        //        'user_id' => $user->id,
        //        'identifier' => 'V'.date('ymd').sprintf('%06d', $user->id),
        //        'birthday' => $birthday,
        //        'sex' => $sex
        //    ]);
        //    if ($node->save() && (!$relation || $relation->save())) {
        //        $saved++;
        //    } else {
        //        $user->delete();
        //    }
        //}
        if ($team_id) {
            $term = Team::find($team_id);
            $term && $term->syncTeamSize();
        }
        $operationNum = count($userTeam) > count($volUser) ? count($userTeam) : count($volUser);
        $totalSkipped = count($rows) - $operationNum - count($newUser) + count($errors);
        $totalDuplicated = count($rows) - $operationNum - count($newUser);
        return $this->returnJSON([
            'saved' => count($newUser),
            'skiped' => $totalSkipped,
            'errors' => $errors,
            'duplicated' => $totalDuplicated
        ], 0, '成功导入'.count($newUser).'条数据!'.($totalSkipped ? '其中总共有'.$totalSkipped.'条数据重复，已跳过。' : '').($totalDuplicated ? ''.$totalDuplicated.'条数据重复被跳过。' : ''));
    }
    public function export(Request $request) {
        $items = $request->input('i');
        if (!is_array($items)) {
            throw new \Exception('数据错误');
        }
        return Excel::create(time(),function($excel) use ($items){
            $excel->sheet('志愿者信息', function($sheet) use ($items){
                $sheet->appendRow(1, array(
                    '姓名', '民族', '身份证号', '手机', '邮箱'
                ));
                foreach($items as $i => $item) {
                    $sheet->appendRow($i+2,array(
                        $item['real_name'],
                        isset($item['nation']) ? $item['nation'] : '',
                        $item['id_number'],
                        $item['phone'],
                        $item['email']
                    ));
                }
            });
        })->export('xls');;
    }

    public function getSchema(Request $request) {
        $user = $request->user();
        $schema = [
            'type' => "crud",
            'api' => "/api/volunteer",
            'columnsTogglable' => false,
            'primaryField' => 'user_id',
            'filter' => [
                'title' => "",
                'submitText' => "",
                'panelClassName' => 'm-b-none bg-gd no-border',
                'bodyClassName' => 'wrapper-sm',
                'mode' => "inline",
                'controls' => [
                    [
                        'type' => "text",
                        'name' => "keyword",
                        'placeholder' => "用户名、联系电话、证件号码",
                        'style' => ['minWidth' => 260],
                        'addOn' => [
                            'type' => "submit",
                            'label' => "搜索",
                            'primary' => true
                        ]
                    ],

                    [
                        'type' => "button",
                        'label' => "导出",
                        'level' => "info",
                        'actionType' => "url",
                        'url' => "/api/volunteer?asExcel=1&keyword=\${keyword}"
                    ]
                ]
            ],
            'toolbarInline' => true,
            'toolbar' => [
                'type' => "button",
                'label' => "全部通知",
                'level' => "info",
                'size' => 'sm',
                'actionType' => "dialog",
                'dialog' => [
                    'title' => "发送站内信",
                    'body' => [
                        'type' => "form",
                        'api' =>  'post:/api/volunteer/notify',
                        'controls' => [
                            [
                                'type' => "textarea",
                                'name' => "content",
                                'label' => false
                            ]
                        ]
                    ]
                ]
            ],
            'bulkActions' => [
                [
                    'type' => "button",
                    'label' => "通知",
                    'actionType' => "dialog",
                    'level' => "info",
                    'dialog' => [
                        'title' => "发送站内信",
                        'body' => [
                            'type' => "form",
                            'api '=> 'post:/api/volunteer/notify/${ids}',
                            'controls' => [
                                [
                                    'type' => "textarea",
                                    'name' => "content",
                                    'label' => false
                                ]
                            ]
                        ]
                    ]
                ],
                [
                    'type' => "button",
                    'label' => "加入黑名单",
                    'actionType' => "dialog",
                    'level' => "danger",
                    'dialog' => [
                        'title' => "请输入拉黑原因",
                        'body' => [
                            'type' => "form",
                            'api' => 'post:/api/volunteer/black/${ids}',
                            'controls' => [
                                [
                                    'type' => "text",
                                    'name' => "reason",
                                    'label' => false
                                ]
                            ]
                        ]
                    ]
                ]
            ]
        ];
        $columns = [
            [
                'name' => "id",
                'label' => "志愿者ID",
                'width' => 50
            ],
            [
                'label' => "志愿者编号",
                'name' => "identifier"
            ],
            [
                'label' => "民族",
                'name' => 'nation'
            ],
            [
                'label' => "姓名",
                'name' => 'real_name',
                'type' => "tpl",
                'tpl' =>  "<a data-link='/volunteer/view/\${data.id}'><%= data.real_name ? data.real_name : '-' %></a>"
            ],
            [
                'label' => "用户名",
                'name' => 'username',
                'type' => "tpl",
                'tpl' =>  "<a data-link='/volunteer/view/\${data.id}'><%= data.username ? data.username : '-' %></a> "
            ],
            [
                'label' => "积分",
                'name' => 'score'
            ],
            [
                'label' => "所属团队",
                'name' => 'team',
                'type' => "tpl",
                'tpl' =>  "<% (Array.isArray(data.team) && data.team.length > 0) ? data.team.map(function(team) {%><div><a data-link='/team/view/<%= team.id %>'><%= team.name %></a></div><%}) : '-' %>"
            ],
            [
                'label' => "性别",
                'name' => 'sex',
                'type' => 'map',
                'map' => [
                    '1' =>  '男',
                    '2' => '女'
                ]
            ],
            [
                'label' => "年龄",
                'name' => 'age'
            ],
            [
                'label' => "居住区域",
                'innerClassName' => 'w',
                'type' => "tpl",
                'tpl' => '${province_name} ${city_name} ${county_name} ${addr}'
            ],
            [
                'label' => "参与项目数",
                'name' => 'project_count'
            ],
            [
                'label' => "志愿时长",
                'name' => 'reward_time'
            ],
            [
                'label' => "注册时间",
                'name' => "created_at",
                'type' => 'tpl',
                'tpl' =>  '${created_at|date:LL:YYYY-MM-DD HH:mm:ss}'
            ],

        ];
        if ($user->type!== 4 && $user->type!== 5
            && ($config = $user->org->custom_config)
            && ($extends = $config->extends)
        ){
            foreach ($extends as $item) {
//                if ($item)
                $key = $item->key;
                switch ($item->type) {
                    case 5:
                        $columns[] = [
                            'name' => $key,
                            'label' => $item->label,
                            'type' => 'image',
                            'popOver' => [
                                'title' => '',
                                'body' => '<div class="w w-xxl"><img class="w-full" src=${'.$key.'} /></div>'
                            ]
                        ];
                        break;
//                    case 6:
//                        $columns[] = [
//                            'name' => $item->key,
//                            'label' => $item->label,
//                            'type' => 'tpl',
//                            'tpl' => '${'.$key.'}|date:LL:YYYY-MM-DD}'
//                        ];
//                        break;
//                    case 7:
//                        $column[] = [
//                            'type' => 'tpl',
//                            'tpl' => '${'.$key.'}|date:LL:YYYY-MM-DD}'
//                        ];
                    default:
                        $columns[] = [
                            'name' => $item->key,
                            'label' => $item->label,
//                            'width' => 120,
                        ];
                }
            }
        }
        $schema['columns'] = $columns;
        return $this->returnJSON($schema, 0, '');

    }

    public function starCertificateAccept(Request $request, $id) {

        $ids = array_map(function($item){
            return intval($item);
        },explode(',', $id));
//        DB::raw('update volunteer set volunteer.stars=volunteer.stars_verify_num, volunteer.stars_verify_status=1')->whereIn('volunteer.id', $ids);
        Volunteer::whereIn('id', $ids)->update([
            'stars' => DB::raw('stars_verify_num'),
            'stars_verify_status' => 1,
        ]);
//        Volunteer::whereIn('id', $ids)->updateRaw();
        return $this->returnJSON($ids, 0, "修改成功");
    }

    public function starCertificateReject(Request $request, $id) {
//        $this->validate($request, [
//
//        ]);

        $ids = array_map(function($item){
            return intval($item);
        },explode(',', $id));

        Volunteer::whereIn('id', $ids)->update([
            'stars_verify_status' => 2
        ]);

        return $this->returnJSON($ids, 0, "修改成功");
    }
}


function checkIdCard($id){
    $id = strtoupper($id); 
    $regx = "/(^\d{15}$)|(^\d{17}([0-9]|X)$)/"; 
    $arr_split = array(); 
    if(!preg_match($regx, $id)) 
    { 
        return FALSE; 
    } 
    if(15==strlen($id)) //检查15位 
    { 
        $regx = "/^(\d{6})+(\d{2})+(\d{2})+(\d{2})+(\d{3})$/"; 
    
        @preg_match($regx, $id, $arr_split); 
        //检查生日日期是否正确 
        $dtm_birth = "19".$arr_split[2] . '/' . $arr_split[3]. '/' .$arr_split[4]; 
        if(!strtotime($dtm_birth)) 
        { 
        return FALSE; 
        } else { 
        return TRUE; 
        } 
    } 
    else      //检查18位 
    { 
        $regx = "/^(\d{6})+(\d{4})+(\d{2})+(\d{2})+(\d{3})([0-9]|X)$/"; 
        @preg_match($regx, $id, $arr_split); 
        $dtm_birth = $arr_split[2] . '/' . $arr_split[3]. '/' .$arr_split[4]; 
        if(!strtotime($dtm_birth)) //检查生日日期是否正确 
        { 
        return FALSE; 
        } 
        else 
        { 
        //检验18位身份证的校验码是否正确。 
        //校验位按照ISO 7064:1983.MOD 11-2的规定生成，X可以认为是数字10。 
        $arr_int = array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2); 
        $arr_ch = array('1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'); 
        $sign = 0; 
        for ( $i = 0; $i < 17; $i++ ) 
        { 
            $b = (int) $id{$i}; 
            $w = $arr_int[$i]; 
            $sign += $b * $w; 
        } 
        $n = $sign % 11; 
        $val_num = $arr_ch[$n]; 
        if ($val_num != substr($id,17, 1)) 
        { 
            return FALSE; 
        } //phpfensi.com 
        else 
        { 
            return TRUE; 
        } 
        } 
    } 

}
